using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class LIST_PRIDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procLIST_PRI_add]";
        private string _strSPUpdateName = "dbo.[procLIST_PRI_update]";
        private string _strSPDeleteName = "dbo.[procLIST_PRI_delete]";
        private string _strSPGetName = "dbo.[procLIST_PRI_get]";
        private string _strSPGetAllName = "dbo.[procLIST_PRI_getall]";
        private string _strSPGetPages = "dbo.[procLIST_PRI_getpaged]";
        private string _strSPIsExist = "dbo.[procLIST_PRI_isexist]";
        private string _strTableName = "[LIST_PRI]";
        private string _strSPGetTransferOutName = "dbo.[procLIST_PRI_gettransferout]";
        private string _strSPGetByIndexName = "procLIST_PRI_getindex";
        private string _strSPGetCountName = "procLIST_PRI_getcount";
        #endregion Local Variable

        #region Method
        public LIST_PRIInfo Get(
        String DTB,
       String P01,
        String P02,
        String P03,
        String P04,
        String P05,
        String P06,
        ref string sErr)
        {
            LIST_PRIInfo objEntr = new LIST_PRIInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter(LIST_PRIInfo.Field.DTB.ToString(), DTB);
            AddParameter(LIST_PRIInfo.Field.P06.ToString(), P06);
            AddParameter(LIST_PRIInfo.Field.P05.ToString(), P05);
            AddParameter(LIST_PRIInfo.Field.P04.ToString(), P04);
            AddParameter(LIST_PRIInfo.Field.P03.ToString(), P03);
            AddParameter(LIST_PRIInfo.Field.P02.ToString(), P02);
            AddParameter(LIST_PRIInfo.Field.P01.ToString(), P01);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = (LIST_PRIInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            LIST_PRIInfo result = new LIST_PRIInfo();
            result.DTB = (dt.Rows[i][LIST_PRIInfo.Field.DTB.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.DTB.ToString()]));
            result.P06 = (dt.Rows[i][LIST_PRIInfo.Field.P06.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.P06.ToString()]));
            result.P05 = (dt.Rows[i][LIST_PRIInfo.Field.P05.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.P05.ToString()]));
            result.P04 = (dt.Rows[i][LIST_PRIInfo.Field.P04.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.P04.ToString()]));
            result.P03 = (dt.Rows[i][LIST_PRIInfo.Field.P03.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.P03.ToString()]));
            result.P02 = (dt.Rows[i][LIST_PRIInfo.Field.P02.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.P02.ToString()]));
            result.P01 = (dt.Rows[i][LIST_PRIInfo.Field.P01.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.P01.ToString()]));
            result.Description = (dt.Rows[i][LIST_PRIInfo.Field.Description.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.Description.ToString()]));
            result.Price01 = (dt.Rows[i][LIST_PRIInfo.Field.Price01.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][LIST_PRIInfo.Field.Price01.ToString()]));
            result.Price02 = (dt.Rows[i][LIST_PRIInfo.Field.Price02.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][LIST_PRIInfo.Field.Price02.ToString()]));
            result.Price03 = (dt.Rows[i][LIST_PRIInfo.Field.Price03.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][LIST_PRIInfo.Field.Price03.ToString()]));
            result.Price04 = (dt.Rows[i][LIST_PRIInfo.Field.Price04.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][LIST_PRIInfo.Field.Price04.ToString()]));
            result.CREATEDBY = (dt.Rows[i][LIST_PRIInfo.Field.CREATEDBY.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.CREATEDBY.ToString()]));
            result.MODIFIEDBY = (dt.Rows[i][LIST_PRIInfo.Field.MODIFIEDBY.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][LIST_PRIInfo.Field.MODIFIEDBY.ToString()]));
            result.CREATEDDATE = (dt.Rows[i][LIST_PRIInfo.Field.CREATEDDATE.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][LIST_PRIInfo.Field.CREATEDDATE.ToString()]));
            result.MODIFIEDDATE = (dt.Rows[i][LIST_PRIInfo.Field.MODIFIEDDATE.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][LIST_PRIInfo.Field.MODIFIEDDATE.ToString()]));

            return result;
        }

        public DataTable GetAll(
        String DTB,
        ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);
            AddParameter(LIST_PRIInfo.Field.DTB.ToString(), DTB);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(LIST_PRIInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter(LIST_PRIInfo.Field.DTB.ToString(), objEntr.DTB);
            AddParameter(LIST_PRIInfo.Field.P06.ToString(), objEntr.P06);
            AddParameter(LIST_PRIInfo.Field.P05.ToString(), objEntr.P05);
            AddParameter(LIST_PRIInfo.Field.P04.ToString(), objEntr.P04);
            AddParameter(LIST_PRIInfo.Field.P03.ToString(), objEntr.P03);
            AddParameter(LIST_PRIInfo.Field.P02.ToString(), objEntr.P02);
            AddParameter(LIST_PRIInfo.Field.P01.ToString(), objEntr.P01);
            AddParameter(LIST_PRIInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(LIST_PRIInfo.Field.Price01.ToString(), objEntr.Price01);
            AddParameter(LIST_PRIInfo.Field.Price02.ToString(), objEntr.Price02);
            AddParameter(LIST_PRIInfo.Field.Price03.ToString(), objEntr.Price03);
            AddParameter(LIST_PRIInfo.Field.Price04.ToString(), objEntr.Price04);
            AddParameter(LIST_PRIInfo.Field.CREATEDBY.ToString(), objEntr.CREATEDBY);
            AddParameter(LIST_PRIInfo.Field.MODIFIEDBY.ToString(), objEntr.MODIFIEDBY);
            AddParameter(LIST_PRIInfo.Field.CREATEDDATE.ToString(), objEntr.CREATEDDATE);
            AddParameter(LIST_PRIInfo.Field.MODIFIEDDATE.ToString(), objEntr.MODIFIEDDATE);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(LIST_PRIInfo objEntr)
        {
            openConnection();
            InitSPCommand(_strSPUpdateName);
            AddParameter(LIST_PRIInfo.Field.DTB.ToString(), objEntr.DTB);
            AddParameter(LIST_PRIInfo.Field.P06.ToString(), objEntr.P06);
            AddParameter(LIST_PRIInfo.Field.P05.ToString(), objEntr.P05);
            AddParameter(LIST_PRIInfo.Field.P04.ToString(), objEntr.P04);
            AddParameter(LIST_PRIInfo.Field.P03.ToString(), objEntr.P03);
            AddParameter(LIST_PRIInfo.Field.P02.ToString(), objEntr.P02);
            AddParameter(LIST_PRIInfo.Field.P01.ToString(), objEntr.P01);
            AddParameter(LIST_PRIInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(LIST_PRIInfo.Field.Price01.ToString(), objEntr.Price01);
            AddParameter(LIST_PRIInfo.Field.Price02.ToString(), objEntr.Price02);
            AddParameter(LIST_PRIInfo.Field.Price03.ToString(), objEntr.Price03);
            AddParameter(LIST_PRIInfo.Field.Price04.ToString(), objEntr.Price04);
            AddParameter(LIST_PRIInfo.Field.CREATEDBY.ToString(), objEntr.CREATEDBY);
            AddParameter(LIST_PRIInfo.Field.MODIFIEDBY.ToString(), objEntr.MODIFIEDBY);
            AddParameter(LIST_PRIInfo.Field.CREATEDDATE.ToString(), objEntr.CREATEDDATE);
            AddParameter(LIST_PRIInfo.Field.MODIFIEDDATE.ToString(), objEntr.MODIFIEDDATE);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        String DTB,
        String P01,
        String P02,
        String P03,
        String P04,
        String P05,
        String P06
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter(LIST_PRIInfo.Field.DTB.ToString(), DTB);
            AddParameter(LIST_PRIInfo.Field.P06.ToString(), P06);
            AddParameter(LIST_PRIInfo.Field.P05.ToString(), P05);
            AddParameter(LIST_PRIInfo.Field.P04.ToString(), P04);
            AddParameter(LIST_PRIInfo.Field.P03.ToString(), P03);
            AddParameter(LIST_PRIInfo.Field.P02.ToString(), P02);
            AddParameter(LIST_PRIInfo.Field.P01.ToString(), P01);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        

        public Boolean IsExist(
        String DTB,
        String P01,
        String P02,
        String P03,
        String P04,
        String P05,
        String P06
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);
            AddParameter(LIST_PRIInfo.Field.DTB.ToString(), DTB);
            AddParameter(LIST_PRIInfo.Field.P06.ToString(), P06);
            AddParameter(LIST_PRIInfo.Field.P05.ToString(), P05);
            AddParameter(LIST_PRIInfo.Field.P04.ToString(), P04);
            AddParameter(LIST_PRIInfo.Field.P03.ToString(), P03);
            AddParameter(LIST_PRIInfo.Field.P02.ToString(), P02);
            AddParameter(LIST_PRIInfo.Field.P01.ToString(), P01);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(LIST_PRIInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public DTO.LIST_PRIInfo GetByPos(string db, int _currentIndex, ref string sErr)
        {
            DTO.LIST_PRIInfo objEntr = new LIST_PRIInfo();
            InitConnect();
            InitSPCommand(_strSPGetByIndexName); 
            AddParameter("INX", _currentIndex);
            AddParameter(LIST_PRIInfo.Field.DTB.ToString(), db);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (list.Rows.Count > 0)
                objEntr = (LIST_PRIInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public int GetCountRecord(string db,ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPGetCountName);
            AddParameter(LIST_PRIInfo.Field.DTB.ToString(), db);
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
    }
}
